#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import os
import pymysql
import logging
import re
from pathlib import Path
import time
import datetime

import sys, os
path = os.path.abspath('../..')+'/ark_project'
sys.path.append(path)  # 会追加到列表最尾部
from settings import my_settings

class run_sql():
    def __init__(self,settings,logger):
        mysql_settings = settings.mysql_conf
        MYSQL_HOST = mysql_settings['MYSQL_HOST']
        MYSQL_PORT = mysql_settings['MYSQL_PORT']
        MYSQL_DB = mysql_settings['MYSQL_DB']
        MYSQL_USER = mysql_settings['MYSQL_USER']
        MYSQL_PASSWD = mysql_settings['MYSQL_PASSWD']
        MYSQL_CHARTSET = mysql_settings['MYSQL_CHARTSET']

        self.curr_path = os.getcwd()+'/'+'ark_run_sql/'
        self.start_date_str = str(time.strftime('%Y-%m-%d', time.localtime()))
        self.logger=logger

        self.connect = pymysql.connect(
            host=MYSQL_HOST,
            port=MYSQL_PORT,
            database=MYSQL_DB,
            user=MYSQL_USER,
            password=MYSQL_PASSWD,
            charset=MYSQL_CHARTSET
        )
        # 使用cursor()方法获取操作游标
        self.cursor = self.connect.cursor()
        self.sql_str=open(self.curr_path+'ARK每日数据更新综合程序.sql','r',encoding='utf-8').read()
        self.run_flag=True

    @staticmethod
    def get_list(date):
        return datetime.datetime.strptime(date, "%Y-%m-%d").timestamp()

    def get_date(self):

        sql_1="SELECT DISTINCT date from ark_sql_trade ORDER BY date Desc LIMIT 3;" #3天
        self.cursor.execute(sql_1)
        ark_trade_date=self.cursor.fetchall()
        ark_trade_date=[t[0] for t in ark_trade_date]
        self.logger.info('ark_sql_trade_date=%s',str(ark_trade_date))

        sql_2 = "SELECT DISTINCT date from ark_sql_holdings ORDER BY date Desc LIMIT 3;" #3天
        self.cursor.execute(sql_2)
        ark_holdings_date=self.cursor.fetchall()
        ark_holdings_date=[t[0] for t in ark_holdings_date]
        self.logger.info('ark_sql_holdings_date=%s',str(ark_holdings_date))

        same_date_list=list(set(ark_trade_date).intersection(set(ark_holdings_date)))
        # same_date_list.sort()   #日期排序，老日期在前

        same_date_list_sort = sorted(same_date_list, key=lambda date: self.get_list(date))
        self.logger.info('same_date_list_sort=%s', str(same_date_list_sort))

        return same_date_list_sort

    def run_sql_list(self,sql_str):
        sql_str = re.sub('\#.*?\n', '\n', sql_str)
        sql_str = re.sub('\-\-.*?\n', '\n', sql_str)
        sql_list = sql_str.split(';')
        for sql in sql_list:
            sql = re.sub('\s+', ' ', sql)
            sql = sql.strip()
            if sql:
                sql = sql + ';'
                # self.logger.info('--succeed sql:%s', sql)
                # self.cursor.execute(sql)
                # self.connect.commit()
                try:
                    self.logger.info('--succeed sql:%s', sql)
                    self.cursor.execute(sql)
                    self.connect.commit()
                    self.logger.info('======succeed commit======')
                except Exception as e:
                    self.logger.error('报错的sql:%s',sql)
                    self.logger.error(e)
                    self.run_flag=False
                    break   #跳出循环
            self.connect.commit()

    def add_ark_cmptkr_cusip_ticker_company_config(self):
        config_list = open(self.curr_path+'ark_cmptkr_cusip_ticker_company_config.txt', 'r', encoding='utf-8').readlines()[1:]
        config_lists = [each.split(',') for each in config_list]
        for config_list in config_lists:
            if len(config_list) == 9:
                # print(config_list)
                sql = """INSERT INTO `ark_cmptkr_cusip` (`company`, `ticker`, `cusip`, `jybcode`, `jybchnname`, `lastprice`, `is_diftcode`, `is_stkshow`, `remark`) 
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s);"""
                try:
                    self.cursor.execute(sql,tuple(config_list))
                    self.connect.commit()
                    self.logger.info('--succeed sql:%s', sql)
                except Exception as e:
                    self.logger.warning('报错的sql:%s', sql)
                    self.logger.warning(e)
            else:
                self.logger.error('==ark_cmptkr_cusip_ticker_company_config配置数据有错：%s',config_list)
                # print('==ark_cmptkr_cusip_ticker_company_config配置数据有错：%s', config_list)
        self.logger.info('-- ark_cmptkr_cusip_ticker_company_config替换配置化 done---')

    def add_ark_trade_ticker_config(self):
        config_list = open(self.curr_path+'ark_trade_ticker_config.txt', 'r', encoding='utf-8').readlines()
        config_dict = {one.split(':')[0].strip(): one.split(':')[1].strip() for one in config_list}
        sql_list = []
        for k, v in config_dict.items():
            sql = "UPDATE `temp_trade` SET `ticker` = '%s' where `ticker` = '%s';" % (k, v)
            sql_list.append(sql)

        return '-- ark_trade_ticker_config替换配置化---开始\n' + \
               '\n'.join(sql_list) + \
               '\n-- ark_trade_ticker_config替换配置化---结束\n'

    def add_ark_holdings_company_config(self):
        config_list = open(self.curr_path+'ark_holdings_company_config.txt', 'r', encoding='utf-8').readlines()
        config_dict = {one.split(':')[0].strip(): one.split(':')[1].strip() for one in config_list}
        sql_list = []
        for k, v in config_dict.items():
            sql = "update temp_holdings set company='%s' where company='%s';" % (k, v)
            sql_list.append(sql)

        return '-- ark_holdings_company_config替换配置化---开始\n' + \
               '\n'.join(sql_list) + \
               '\n-- ark_holdings_company_config替换配置化---结束\n'

    def execute_sql(self):
        same_date_list=self.get_date()
        for same_date in same_date_list:
            #ark_cmptkr_cusip_ticker_company配置化数据部分的sql单独执行
            self.add_ark_cmptkr_cusip_ticker_company_config()

            self.sql_str = re.sub("set @today='[\s\S]+?';", "set @today='" + same_date + "';", self.sql_str)

            self.sql_str = re.sub('-- ark_trade_ticker_config替换配置化---开始[\s\S]+?-- ark_trade_ticker_config替换配置化---结束',
                                  self.add_ark_trade_ticker_config(), self.sql_str)
            self.sql_str = re.sub('-- ark_holdings_company_config替换配置化---开始[\s\S]+?-- ark_holdings_company_config替换配置化---结束',
                                  self.add_ark_holdings_company_config(), self.sql_str)

            self.run_sql_list(self.sql_str)

        self.cursor.close()
        self.connect.close()

if __name__=='__main__':
    rs=run_sql(log_rank=logging.INFO)
    rs.execute_sql()